#dbutils.py -- Database utility functions for uPWiki
#Copyright 2006 Jacques Richer
#
#This program is free software; you can redistribute it and/or
#modify it under the terms of the GNU General Public License
#as published by the Free Software Foundation; either version 2
#of the License, or (at your option) any later version.
#
#This program is distributed in the hope that it will be useful,
#but WITHOUT ANY WARRANTY; without even the implied warranty of
#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#GNU General Public License for more details.
#
#You should have received a copy of the GNU General Public License
#along with this program; if not, write to the Free Software
#Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.

from pysqlite2 import dbapi2 as sqlite
import BHS
import threading
from types import *

def db_init(dbname):
	con=sqlite.connect(dbname, timeout=0.5)
	cur=con.cursor()
	
	cur.executescript("""
	CREATE TABLE pages(
		id		INTEGER PRIMARY KEY AUTOINCREMENT,
		title		CHAR NOT NULL,
		contents	CLOB,
		revnote	CLOB
	);

	CREATE TABLE pagelist(
		key		CHAR PRIMARY KEY,
		value	CHAR
	);

	CREATE TABLE config(
		key		CHAR NOT NULL,
		value	CHAR
	);
	
	CREATE TABLE strings(
		key		CHAR NOT NULL,
		value	CLOB
	);""")

	db_inserts(cur, BHS.strings_inserts, 'strings')
	db_inserts(cur, BHS.config_inserts, 'config')
	con.commit()

def db_inserts(cur, inserts, table):
	for (key, val) in inserts.items(): 
		cur.execute("INSERT OR REPLACE INTO %s (key, value) VALUES (?, ?);" % table, (key, val))

class SQLHash:
	def __init__(self, dbname, table):
		self.dbname = dbname
		self.table = table
		self.itemsem = threading.Semaphore()
		self.hdata = {}
		con = sqlite.connect(self.dbname, timeout=5.0)
		cur = con.cursor()
		cur.execute("SELECT key, value FROM %s;" % (self.table))
		for (key, value) in cur.fetchall():
			self.hdata[key] = value
		cur.close()
		con.close()
	
	def __getitem__(self, key):
		self.itemsem.acquire()
		if key in self.hdata:
			x=self.hdata[key]
		else:
			x=None
		self.itemsem.release()
		return x
		
	def __setitem__(self, key, value):
		self.itemsem.acquire()
		con = sqlite.connect(self.dbname, timeout=5.0)
		cur = con.cursor()
		cur.execute("INSERT OR REPLACE INTO %s (key, value) VALUES (?, ?);" % (self.table), (key, value))
		con.commit()
		self.hdata[key]=value
		
		self.itemsem.release()
		return value

class SQLPage:
	def __init__(self, dbname):
		self.dbname = dbname
		self.pagelist = SQLHash(dbname, 'pagelist')
		self.pagesem = threading.Semaphore()
	
	def ispage(self, pagename):
		self.pagesem.acquire()
		x=self.pagelist[pagename]
		self.pagesem.release()
		return x
		
	def _ispage(self, pagename):
		x=self.pagelist[pagename]
		return x
		
	def get_previous(self, pagename, rev=None):
		self.pagesem.acquire()
		if rev == None:
			rev = self.pagelist[pagename]
		if rev == None:
			self.pagesem.release()
			return None
		con = sqlite.connect(self.dbname, timeout=5.0)
		cur = con.cursor()
		cur.execute("SELECT id, title, contents FROM pages WHERE id = (SELECT max(id) FROM pages WHERE title = ? and id < ?);", (pagename, rev))
		x = cur.fetchone()
		if x:
			(id, title, contents) = x
		else:
			cur.close()
			con.close()
			self.pagesem.release()
			return None
		cur.close()
		con.close()
		self.pagesem.release()
		return (contents, id)

	def get_next(self, pagename, rev):
		self.pagesem.acquire()
		if rev < self._ispage(pagename):
			con = sqlite.connect(self.dbname, timeout=5.0)
			cur = con.cursor()
			cur.execute("SELECT id, title, contents FROM pages WHERE id = (SELECT min(id) FROM pages WHERE title = ? and id > ?);", (pagename, rev))
			x=cur.fetchone()
			if x:
				(id, title, contents) = x
			else:
				cur.close()
				con.close()
				self.pagesem.release()
				return None
			cur.close()
			con.close()
		else:
			self.pagesem.release()
			return None
		self.pagesem.release()
		return (contents, id)
	
	def get_first(self, pagename):
		return self.get_next(pagename, 0)
	
	def get_last(self, pagename):
		return self.__getitem__(pagename)

	def get_item(self, pagename, rev=None):
		return self.__getitem__(pagename, rev)

	def get_revisions(self, pagename):
		self.pagesem.acquire()
		if not self._ispage(pagename):
			self.pagesem.release()
			return None
		con = sqlite.connect(self.dbname, timeout=5.0)
		cur = con.cursor()
		cur.execute("SELECT id, title, revnote FROM pages WHERE title = ? ORDER BY id DESC;", (pagename,))
		x=cur.fetchall()
		cur.close()
		con.close()
		self.pagesem.release()
		return x

	def __getitem__(self, pagename, rev=None):
		self.pagesem.acquire()
		if rev == None:
			rev=self._ispage(pagename)
		if rev > self._ispage(pagename):
			self.pagesem.release()
			return None
		if rev != None:
			con = sqlite.connect(self.dbname, timeout=5.0)
			cur = con.cursor()
			cur.execute("SELECT id, title, contents FROM pages WHERE id = ? and title = ?;", (rev, pagename))
			x=cur.fetchone()
			if x:
				(id, title, contents) = x
			else:
				cur.close()
				con.close()
				self.pagesem.release()
				return None
			cur.close()
			con.close()
		else:
			self.pagesem.release()
			return None
		self.pagesem.release()
		return (contents, id)

	def __setitem__(self, pagename, contents):
		self.pagesem.acquire()
		con = sqlite.connect(self.dbname, timeout=5.0)
		cur = con.cursor()
		if type(contents) == TupleType:
			(pagedata, revnote) = contents
			if revnote == "":
				revnote = "-- No comment listed --"
		else:
			pagedata = contents
			revnote = "-- No comment listed --"
		cur.execute("INSERT INTO pages (title, contents, revnote) VALUES (?, ?, ?);", (pagename, pagedata, revnote))
		con.commit()
		
		self.pagelist[pagename]=cur.lastrowid
		self.pagesem.release()
		return True
	



